const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const db = new sqlite3.Database('todos.db');

db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    completed BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);
});

class Todo {
  static getAll(callback) {
    db.all('SELECT * FROM todos ORDER BY created_at DESC', callback);
  }

  static getById(id, callback) {
    db.get('SELECT * FROM todos WHERE id = ?', [id], callback);
  }

  static create(title, callback) {
    db.run('INSERT INTO todos (title) VALUES (?)', [title], function(err) {
      callback(err, { id: this.lastID, title, completed: false });
    });
  }

  static update(id, data, callback) {
    const { title, completed } = data;
    db.run(
      'UPDATE todos SET title = COALESCE(?, title), completed = COALESCE(?, completed) WHERE id = ?',
      [title, completed, id],
      callback
    );
  }

  static delete(id, callback) {
    db.run('DELETE FROM todos WHERE id = ?', [id], callback);
  }
}

module.exports = { db, Todo };
